#coding=utf-8
#coding=gbk
#coding=gb2312
# import ttkbootstrap as ttk
# from ttkbootstrap.constants import *
# import tkinter as tk
# from tkinter import messagebox

import tkinter as tk
from tkinter import messagebox, ttk
import redis
import mysql
from database import Database
import re





class LoginWindow(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("超市进销存管理系统--用户登录")
        self.geometry("500x200")

        self.username_label = ttk.Label(self, text="员工ID:")
        self.username_label.pack(pady=5)
        self.username_entry = ttk.Entry(self)
        self.username_entry.pack(pady=5)

        self.password_label = ttk.Label(self, text="密码:")
        self.password_label.pack(pady=5)
        self.password_entry = ttk.Entry(self, show="*")
        self.password_entry.pack(pady=5)

        self.login_button = ttk.Button(self, text="登录", command=self.check_login)
        self.login_button.pack(pady=20)

        # 连接到 Redis 服务器
        self.redis_client = redis.Redis(host='localhost', port=6379, db=0)

    def check_login(self):
        username = self.username_entry.get()
        password = self.password_entry.get()

        # 尝试从 Redis 缓存中获取用户信息
        cached_data = self.redis_client.get(username)
        if cached_data:
            cached_password = cached_data.decode('utf-8')
            if cached_password == password:
                self.destroy()  # 关闭登录窗口
                app = Application()
                app.mainloop()
                return

        # 如果缓存中没有数据或者密码不匹配，查询数据库
        db = Database()
        query = "SELECT * FROM tb_employee WHERE Eid = %s AND Epas = %s"
        result = db.fetch_one(query, (username, password))

        if result:

            # 将用户信息存入 Redis 缓存，有效期设置为一小时（3600 秒）
            eid, ename, epas, elevel, etelphone, esalary, other = result  # 假设查询结果包括这些字段
            if elevel == '00':  # 判断是否是管理员
                # 将用户信息存入 Redis 缓存，有效期设置为一小时（3600 秒）
                self.redis_client.setex(username, 3600, password)
                self.destroy()  # 关闭登录窗口
                app = Application()
                app.mainloop()
            else:
                messagebox.showerror("登录失败", "您不是管理员，不能登录")
        else:
            messagebox.showerror("登录失败", "ID或密码错误")

class Application(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title("超市进销存管理系统")
        self.geometry("800x600") #主窗口大小
        self.db = Database()#创建一个database的实例

        # 创建多选项卡界面
        self.notebook = ttk.Notebook(self)#主窗口控件实例
        self.notebook.pack(pady=10, expand=True)#设置大小，竖向固定，横向可扩展

        # 创建n个选项卡
        self.create_customer_tab()
        self.create_product_tab()
        self.create_employee_tab()
        self.create_purchase_main_tab()
        self.create_purchase_detail_tab()
        self.create_price_query_tab()
        self.create_good_inventory_tab()




######          客户管理        ################################
    def create_customer_tab(self):
        self.customer_tab = ttk.Frame(self.notebook)
        self.notebook.add(self.customer_tab, text='   客户管理   ')

        form_frame = ttk.LabelFrame(self.customer_tab, text="  客户表  ")
        form_frame.pack(fill="both", expand="no", padx=20, pady=8)
        self.customer_form(form_frame)

        list_frame = ttk.LabelFrame(self.customer_tab, text="  客户列表  ")
        list_frame.pack(fill="both", expand="yes", padx=20, pady=5)
        self.customer_list(list_frame)

        search_frame = ttk.Frame(self.customer_tab)
        search_frame.pack(fill="x", padx=20, pady=10)

        self.customer_search_label = ttk.Label(search_frame, text="搜索公司名称:")
        self.customer_search_label.pack(side="left", padx=(0, 10))

        self.customer_search_entry = ttk.Entry(search_frame)
        self.customer_search_entry.pack(side="left", padx=(0, 10))

        self.customer_search_button = ttk.Button(search_frame, text="搜索", command=self.search_customer)
        self.customer_search_button.pack(side="left")

    def customer_form(self, frame):
        labels = ["ID", "公司名称", "公司名简称", "地址", "电话号", "邮箱", "联系人姓名", "联系人电话", "备注"]
        self.entries = {}

        for i, label in enumerate(labels):
            ttk.Label(frame, text=label).grid(row=i, column=0, padx=10, pady=5, sticky="w")
            entry = ttk.Entry(frame, width=30)
            entry.grid(row=i, column=1, padx=10, pady=5, sticky="w")
            self.entries[label] = entry

        ttk.Button(frame, text="添加", command=self.add_customer).grid(row=len(labels), column=0, padx=10, pady=10)
        ttk.Button(frame, text="更新", command=self.update_customer).grid(row=len(labels), column=1, padx=10, pady=10)

    def customer_list(self, frame):
        columns = ["ID", "公司名称", "公司名简称", "地址", "电话号", "邮箱", "联系人姓名", "联系人电话", "备注"]

        # 创建一个框架用来放置滚动条，就叫tree_frame
        tree_frame = ttk.Frame(frame)
        tree_frame.pack(fill="both", expand=True)

        # 在刚创建的框架里创建一个小部件
        self.tree = ttk.Treeview(tree_frame, columns=columns, show='headings')

        # 垂直滚动条vbs，绑定到部件
        # vsb = ttk.Scrollbar(tree_frame, orient="vertical", command=self.tree.yview)
        # self.tree.configure(yscrollcommand=vsb.set)
        # vsb.pack(side='right', fill='y')

        # 水平滚动条
        hsb = ttk.Scrollbar(tree_frame, orient="horizontal", command=self.tree.xview)
        self.tree.configure(xscrollcommand=hsb.set)
        hsb.pack(side='bottom', fill='x')

        self.tree.pack(fill="both", expand=True)
        self.tree.bind("<MouseWheel>", self._on_mousewheel)  # 绑定鼠标事件

        for col in columns:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=100)

        self.load_customers()
        self.tree.bind("<Double-1>", self.on_tree_select)




        ttk.Button(frame, text="删除", command=self.delete_customer).pack(side=tk.RIGHT, padx=10, pady=10)
        ttk.Button(frame, text="导出为CSV文件", command=self.export_customers).pack(side=tk.RIGHT, padx=10, pady=10)

    def search_customer(self):
        search_term = self.customer_search_entry.get().lower()

        for item in self.tree.get_children():
            self.tree.item(item, tags=())

        for item in self.tree.get_children():
            customer = self.tree.item(item, "values")
            if search_term in customer[1].lower():
                self.tree.item(item, tags=('found',))
                self.tree.selection_set(item)
                self.tree.see(item)
                return

        messagebox.showinfo("搜索结果", "未找到匹配的公司")
    def _on_mousewheel(self, event):#鼠标事件
        self.tree.yview_scroll(int(-1 * (event.delta / 120)), "units")
    def load_customers(self):
        for i in self.tree.get_children():
            self.tree.delete(i)

        customers = self.db.fetch_all("SELECT * FROM tb_customer")

        for customer in customers:
            self.tree.insert('', tk.END, values=customer)

    def validate_fields(self, data):
        required_fields = ["ID", "公司名称", "公司名简称", "地址", "联系人姓名", "联系人电话"]
        empty_fields = [key for i, key in enumerate(self.entries) if key in required_fields and not data[i]]

        if empty_fields:
            messagebox.showerror("错误", f"以下字段不能为空: {', '.join(empty_fields)}")
            return False
        return True

    def add_customer(self):
        data = [entry.get() for entry in self.entries.values()]

        if not self.validate_fields(data):
            return

        if not re.match(r'^gys\d{4}$', data[0]):
            messagebox.showerror("错误", "ID格式错误，请重新输入正确的ID（例如：gys1234）")
            return

        query = """
        INSERT INTO tb_customer (Cid, CcompanyName, CcompanySName, CcompanyAddress, CcompanyPhone, Cemail, CName, CtelPhone, other)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        try:
            self.db.execute_query(query, data)
            self.load_customers()
        except Exception as e:
            messagebox.showerror("数据库错误", f"数据库操作失败：{str(e)}")

    def update_customer(self):
        selected_item = self.tree.selection()[0]
        data = [entry.get() for entry in self.entries.values()]
        data.append(self.tree.item(selected_item)['values'][0])


        if not self.validate_fields(data):
            return
        data.pop(0)
        query = """
        UPDATE tb_customer SET CcompanyName=%s, CcompanySName=%s, CcompanyAddress=%s, CcompanyPhone=%s, Cemail=%s, CName=%s, CtelPhone=%s, other=%s
        WHERE Cid=%s
        """
        self.db.execute_query(query, data)
        self.load_customers()

    def delete_customer(self):
        selected_item = self.tree.selection()[0]
        Cid = self.tree.item(selected_item)['values'][0]
        query = "DELETE FROM tb_customer WHERE Cid=%s"
        self.db.execute_query(query, (Cid,))
        self.load_customers()

    def on_tree_select(self, event):
        selected_item = self.tree.selection()[0]
        customer = self.tree.item(selected_item)['values']
        for i, key in enumerate(self.entries):
            self.entries[key].delete(0, tk.END)
            self.entries[key].insert(0, customer[i])

    def export_customers(self):
        import csv
        with open('客户信息.csv', 'w', newline='') as file:
            writer = csv.writer(file)
            writer.writerow([col for col in self.entries])
            for customer in self.db.fetch_all("SELECT * FROM tb_customer"):
                writer.writerow(customer)
        messagebox.showinfo("导出", "导出到Customer.csv的客户数据")




#######             产品管理        ##########################################
    def create_product_tab(self):
        self.product_tab = ttk.Frame(self.notebook)
        self.notebook.add(self.product_tab, text='   产品管理   ')

        form_frame = ttk.LabelFrame(self.product_tab, text="  产品表单  ")
        form_frame.pack(fill="both", expand="yes", padx=20, pady=10)

        self.product_form(form_frame)

        list_frame = ttk.LabelFrame(self.product_tab, text="  产品列表  ")
        list_frame.pack(fill="both", expand="yes", padx=20, pady=10)

        self.product_list(list_frame)
        # 在这里添加搜索框和按钮
        search_frame = ttk.Frame(self.product_tab)
        search_frame.pack(fill="x", padx=20, pady=10)

        self.search_label = ttk.Label(search_frame, text="搜索商品名称:")
        self.search_label.pack(side="left", padx=(0, 10))

        self.search_entry = ttk.Entry(search_frame)
        self.search_entry.pack(side="left", padx=(0, 10))

        self.search_button = ttk.Button(search_frame, text="搜索", command=self.search_product)
        self.search_button.pack(side="left")

    def search_product(self):
        search_term = self.search_entry.get().lower()

        for item in self.product_tree.get_children():
            self.product_tree.item(item, tags=())

        for item in self.product_tree.get_children():
            product = self.product_tree.item(item, "values")
            if search_term in product[1].lower():
                self.product_tree.item(item, tags=('found',))
                self.product_tree.selection_set(item)
                self.product_tree.see(item)
                return

        messagebox.showinfo("搜索结果", "未找到匹配的商品")
    def product_form(self, frame):
        labels = ["商品ID", "商品名", "价格", "供应商ID", "产品介绍", "备注"]
        self.product_entries = {}

        for i, label in enumerate(labels):
            ttk.Label(frame, text=label).grid(row=i, column=0, padx=10, pady=5, sticky="w")
            entry = ttk.Entry(frame, width=30)
            entry.grid(row=i, column=1, padx=10, pady=5, sticky="w")
            self.product_entries[label] = entry

        ttk.Button(frame, text="添加", command=self.add_product).grid(row=len(labels), column=0, padx=10, pady=10)
        ttk.Button(frame, text="更新", command=self.update_product).grid(row=len(labels), column=1, padx=10, pady=10)

    def product_list(self, frame):
        columns = ["商品ID", "商品名", "价格", "供应商ID", "产品介绍", "备注"]
        self.product_tree = ttk.Treeview(frame, columns=columns, show='headings')
        self.product_tree.pack(fill="both", expand=True)

        tree_frame = ttk.Frame(frame)
        tree_frame.pack(fill="both", expand=True)



        hsb = ttk.Scrollbar(tree_frame, orient="horizontal", command=self.product_tree.xview)
        self.product_tree.configure(xscrollcommand=hsb.set)
        hsb.pack(side='bottom', fill='x')

        self.product_tree.pack(fill="both", expand=True)
        self.product_tree.bind("<MouseWheel>", self._on_mousewheel)  # 绑定鼠标事件
        for col in columns:
            self.product_tree.heading(col, text=col)
            self.product_tree.column(col, width=100)

        self.load_products()
        self.product_tree.bind("<Double-1>", self.on_product_tree_select)

        ttk.Button(frame, text="删除", command=self.delete_product).pack(side=tk.RIGHT, padx=10, pady=10)
        ttk.Button(frame, text="导出为CSV文件", command=self.export_products).pack(side=tk.RIGHT, padx=10, pady=10)

    def load_products(self):
        for i in self.product_tree.get_children():
            self.product_tree.delete(i)

        products = self.db.fetch_all("SELECT * FROM tb_good")
        for product in products:
            self.product_tree.insert('', tk.END, values=product)

    def _on_mousewheel_highlight(self, event):
        self.product_tree.yview_scroll(int(-1 * (event.delta / 120)), "units")

    def validate_product_fields(self, data):
        required_fields = ["商品ID", "商品名", "价格", "供应商ID"]
        empty_fields = [key for i, key in enumerate(self.product_entries) if key in required_fields and not data[i]]

        if empty_fields:
            messagebox.showerror("错误", f"以下字段不能为空: {', '.join(empty_fields)}")
            return False
        return True

    def add_product(self):
        data = [entry.get() for entry in self.product_entries.values()]

        if not re.match(r'^sp\d{8}$', data[0]):
            messagebox.showerror("错误", "ID格式错误，请重新输入正确的ID（例如：sp00000001）")
            return
        if not self.validate_product_fields(data):
            return

        query = """
        INSERT INTO tb_good (Gid, GName, GPay, Cid, GIntroduction, other)
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        self.db.execute_query(query, data)
        self.load_products()

    def update_product(self):
        selected_item = self.product_tree.selection()[0]
        data = [entry.get() for entry in self.product_entries.values()]
        data.append(self.product_tree.item(selected_item)['values'][0])


        if not self.validate_product_fields(data):
            return
        data.pop(0)
        query = """
        UPDATE tb_good SET GName=%s, GPay=%s, Cid=%s, GIntroduction=%s, other=%s
        WHERE Gid=%s
        """
        self.db.execute_query(query, data)
        self.load_products()

    def delete_product(self):
        selected_item = self.product_tree.selection()[0]
        Gid = self.product_tree.item(selected_item)['values'][0]
        query = "DELETE FROM tb_good WHERE Gid=%s"
        self.db.execute_query(query, (Gid,))
        self.load_products()

    def on_product_tree_select(self, event):
        selected_item = self.product_tree.selection()[0]
        product = self.product_tree.item(selected_item)['values']
        for i, key in enumerate(self.product_entries):
            self.product_entries[key].delete(0, tk.END)
            self.product_entries[key].insert(0, product[i])

    def export_products(self):
        import csv
        with open('产品.csv', 'w', newline='') as file:
            writer = csv.writer(file)
            writer.writerow([col for col in self.product_entries])
            for product in self.db.fetch_all("SELECT * FROM tb_good"):
                writer.writerow(product)
        messagebox.showinfo("导出", "导出产品数据到‘产品.csv’")



############          员工管理         #############################


    def create_employee_tab(self):
        self.employee_tab = ttk.Frame(self.notebook)
        self.notebook.add(self.employee_tab, text='   员工管理   ')

        form_frame = ttk.LabelFrame(self.employee_tab, text="  员工表单  ")
        form_frame.pack(fill="both", expand="yes", padx=20, pady=10)

        self.employee_form(form_frame)

        list_frame = ttk.LabelFrame(self.employee_tab, text="  员工列表  ")
        list_frame.pack(fill="both", expand="yes", padx=20, pady=10)

        self.employee_list(list_frame)

        search_frame = ttk.Frame(self.employee_tab)
        search_frame.pack(fill="x", padx=20, pady=10)

        self.employee_search_label = ttk.Label(search_frame, text="搜索员工姓名:")
        self.employee_search_label.pack(side="left", padx=(0, 10))

        self.employee_search_entry = ttk.Entry(search_frame)
        self.employee_search_entry.pack(side="left", padx=(0, 10))

        self.employee_search_button = ttk.Button(search_frame, text="搜索", command=self.search_employee)
        self.employee_search_button.pack(side="left")

    def employee_form(self, frame):
        labels = ["员工ID", "员工姓名", "登录密码", "级别", "电话号", "工资", "备注"]
        self.employee_entries = {}

        for i, label in enumerate(labels):
            ttk.Label(frame, text=label).grid(row=i, column=0, padx=10, pady=5, sticky="w")
            entry = ttk.Entry(frame, width=30)
            entry.grid(row=i, column=1, padx=10, pady=5, sticky="w")
            self.employee_entries[label] = entry

        ttk.Button(frame, text="添加", command=self.add_employee).grid(row=len(labels), column=0, padx=10, pady=10)
        ttk.Button(frame, text="更新", command=self.update_employee).grid(row=len(labels), column=1, padx=10, pady=10)

    def employee_list(self, frame):
        columns = ["员工ID", "员工姓名", "登录密码", "级别", "电话号", "工资", "备注"]
        self.employee_tree = ttk.Treeview(frame, columns=columns, show='headings')
        self.employee_tree.pack(fill="both", expand=True)

        for col in columns:
            self.employee_tree.heading(col, text=col)
            self.employee_tree.column(col, width=100)

        self.load_employees()
        self.employee_tree.bind("<Double-1>", self.on_employee_tree_select)

        ttk.Button(frame, text="删除", command=self.delete_employee).pack(side=tk.RIGHT, padx=10, pady=10)
        ttk.Button(frame, text="导出为csv文件", command=self.export_employees).pack(side=tk.RIGHT, padx=10, pady=10)

    def search_employee(self):
        search_term = self.employee_search_entry.get().lower()

        for item in self.employee_tree.get_children():
            self.employee_tree.item(item, tags=())

        for item in self.employee_tree.get_children():
            employee = self.employee_tree.item(item, "values")
            if search_term in employee[1].lower():
                self.employee_tree.item(item, tags=('found',))
                self.employee_tree.selection_set(item)
                self.employee_tree.see(item)
                return

        messagebox.showinfo("搜索结果", "未找到匹配的员工")
    def load_employees(self):
        for i in self.employee_tree.get_children():
            self.employee_tree.delete(i)

        employees = self.db.fetch_all("SELECT * FROM tb_employee")
        for employee in employees:
            self.employee_tree.insert('', tk.END, values=employee)

    def validate_employee_fields(self, data):
        required_fields = [ "员工姓名", "登录密码", "级别", "电话号"]
        empty_fields = [key for i, key in enumerate(self.employee_entries) if key in required_fields and not data[i]]

        if empty_fields:
            messagebox.showerror("错误", f"以下字段不能为空: {', '.join(empty_fields)}")
            return False
        return True
    def add_employee(self):
        data = [entry.get() for entry in self.employee_entries.values()]

        if not self.validate_employee_fields(data):
            return

        query = """
        INSERT INTO tb_employee (Eid, EName, EPas, Elevel, EtelPhone, ESalary, other)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        self.db.execute_query(query, data)
        self.load_employees()

        # 清空输入框内容
        for entry in self.employee_entries.values():
            entry.delete(0, tk.END)

    def update_employee(self):
        selected_item = self.employee_tree.selection()[0]
        data = [entry.get() for entry in self.employee_entries.values()]
        data.append(self.employee_tree.item(selected_item)['values'][0])

        if not self.validate_employee_fields(data):
            return

        query = """
        UPDATE tb_employee SET EName=%s, EPas=%s, Elevel=%s, EtelPhone=%s, ESalary=%s, other=%s
        WHERE Eid=%s
        """
        self.db.execute_query(query, data)
        self.load_employees()


        for entry in self.employee_entries.values():
            entry.delete(0, tk.END)


    def delete_employee(self):
        selected_item = self.employee_tree.selection()[0]
        Eid = self.employee_tree.item(selected_item)['values'][0]
        query = "DELETE FROM tb_employee WHERE Eid=%s"
        self.db.execute_query(query, (Eid,))
        self.load_employees()

    def on_employee_tree_select(self, event):
        selected_item = self.employee_tree.selection()[0]
        employee = self.employee_tree.item(selected_item)['values']
        for i, key in enumerate(self.employee_entries):
            self.employee_entries[key].delete(0, tk.END)
            self.employee_entries[key].insert(0, employee[i])

    def export_employees(self):
        import csv
        with open('员工.csv', 'w', newline='') as file:
            writer = csv.writer(file)
            writer.writerow([col for col in self.employee_entries])
            for employee in self.db.fetch_all("SELECT * FROM tb_employee"):
                writer.writerow(employee)
        messagebox.showinfo("导出", "导出员工信息到‘员工.csv’")

    #设置完每次在输入框




    #########      采购主表            #################################


    def create_purchase_main_tab(self):
        self.purchase_main_tab = ttk.Frame(self.notebook)
        self.notebook.add(self.purchase_main_tab, text='   采购主表管理   ')

        form_frame = ttk.Frame(self.purchase_main_tab)
        form_frame.pack(fill="both", expand="yes", padx=20, pady=10)

        main_form_frame = ttk.LabelFrame(form_frame, text="  采购主表表单  ")
        main_form_frame.pack(side="left", fill="both", expand=True, padx=10, pady=10)
        self.purchase_main_form(main_form_frame)

        main_list_frame = ttk.LabelFrame(self.purchase_main_tab, text="  采购主表列表  ")
        main_list_frame.pack(fill="both", expand="yes", padx=20, pady=10)
        self.purchase_main_list(main_list_frame)

        search_frame = ttk.Frame(self.purchase_main_tab)
        search_frame.pack(fill="x", padx=20, pady=10)

        self.purchase_main_search_label = ttk.Label(search_frame, text="搜索采购ID:")
        self.purchase_main_search_label.pack(side="left", padx=(0, 10))

        self.purchase_main_search_entry = ttk.Entry(search_frame)
        self.purchase_main_search_entry.pack(side="left", padx=(0, 10))

        self.purchase_main_search_button = ttk.Button(search_frame, text="搜索", command=self.search_purchase_main)
        self.purchase_main_search_button.pack(side="left")

    def purchase_main_form(self, frame):
        labels = ["采购ID", "员工ID",  "采购时间", "备注"]
        self.purchase_main_entries = {}

        for i, label in enumerate(labels):
            ttk.Label(frame, text=label).grid(row=i, column=0, padx=10, pady=5, sticky="w")
            entry = ttk.Entry(frame, width=30)
            entry.grid(row=i, column=1, padx=10, pady=5, sticky="w")
            self.purchase_main_entries[label] = entry

        ttk.Button(frame, text="添加", command=self.add_purchase_main).grid(row=len(labels), column=0, padx=10, pady=10)
        ttk.Button(frame, text="更新", command=self.update_purchase_main).grid(row=len(labels), column=1, padx=10,
                                                                               pady=10)

    def purchase_main_list(self, frame):
        columns = ["采购ID", "员工ID", "采购数量", "采购总价", "采购时间", "备注"]
        self.purchase_main_tree = ttk.Treeview(frame, columns=columns, show='headings')
        self.purchase_main_tree.pack(fill="both", expand=True)

        tree_frame = ttk.Frame(frame)
        tree_frame.pack(fill="both", expand=True)

        # vsb = ttk.Scrollbar(tree_frame, orient="vertical", command=self.purchase_main_tree.yview)
        # self.purchase_main_tree.configure(yscrollcommand=vsb.set)
        # vsb.pack(side='right', fill='y')

        hsb = ttk.Scrollbar(tree_frame, orient="horizontal", command=self.purchase_main_tree.xview)
        self.purchase_main_tree.configure(xscrollcommand=hsb.set)
        hsb.pack(side='bottom', fill='x')

        self.purchase_main_tree.pack(fill="both", expand=True)
        self.purchase_main_tree.bind("<MouseWheel>", self._on_mousewheel)

        for col in columns:
            self.purchase_main_tree.heading(col, text=col)
            self.purchase_main_tree.column(col, width=100)

        self.load_purchase_main()
        self.purchase_main_tree.bind("<Double-1>", self.on_purchase_main_tree_select)

        ttk.Button(frame, text="删除", command=self.delete_purchase_main).pack(side=tk.RIGHT, padx=10, pady=10)
        ttk.Button(frame, text="导出为csv文件", command=self.export_purchase_main).pack(side=tk.RIGHT, padx=10, pady=10)

    def load_purchase_main(self):
        for i in self.purchase_main_tree.get_children():
            self.purchase_main_tree.delete(i)

        purchases = self.db.fetch_all("SELECT * FROM tb_pay_main")
        for purchase in purchases:
            self.purchase_main_tree.insert('', tk.END, values=purchase)

    def validate_purchase_main_fields(self, data):
        required_fields = ["采购ID", "员工ID", "采购时间"]
        empty_fields = [key for i, key in enumerate(self.purchase_main_entries) if
                        key in required_fields and not data[i]]

        if empty_fields:
            messagebox.showerror("错误", f"以下字段不能为空: {', '.join(empty_fields)}")
            return False
        return True

    def add_purchase_main(self):
        data = [entry.get() for entry in self.purchase_main_entries.values()]

        data = [item if item != '' else None for item in data]

        if not self.validate_purchase_main_fields(data):
            return

        query = """
        INSERT INTO tb_pay_main (Pid, Eid,  Pdate, other)
        VALUES (%s, %s, %s, %s)
        """
        try:
            self.db.execute_query(query, data)
        except mysql.connector.Error as err:
            messagebox.showerror("错误", f"数据库错误: {err}")
        self.load_purchase_main()


    def search_purchase_main(self):
        search_term = self.purchase_main_search_entry.get().lower()

        for item in self.purchase_main_tree.get_children():
            self.purchase_main_tree.item(item, tags=())

        for item in self.purchase_main_tree.get_children():
            purchase_main = self.purchase_main_tree.item(item, "values")
            if search_term in purchase_main[0].lower():
                self.purchase_main_tree.item(item, tags=('found',))
                self.purchase_main_tree.selection_set(item)
                self.purchase_main_tree.see(item)
                return

        messagebox.showinfo("搜索结果", "未找到匹配的采购ID")


    def update_purchase_main(self):
        selected_item = self.purchase_main_tree.selection()[0]
        data = [entry.get() for entry in self.purchase_main_entries.values()]
        data.append(self.purchase_main_tree.item(selected_item)['values'][0])


        if not self.validate_purchase_main_fields(data):
            return
        data.pop(0)

        query = """
        UPDATE tb_pay_main SET Eid=%s, Pcount=%s, Ptotal=%s, Pdate=%s, other=%s
        WHERE Pid=%s
        """
        self.db.execute_query(query, data)
        self.load_purchase_main()

    def delete_purchase_main(self):
        selected_item = self.purchase_main_tree.selection()[0]
        Pid = self.purchase_main_tree.item(selected_item)['values'][0]
        query = "DELETE FROM tb_pay_main WHERE Pid=%s"
        self.db.execute_query(query, (Pid,))
        self.load_purchase_main()

    def on_purchase_main_tree_select(self, event):
        selected_item = self.purchase_main_tree.selection()[0]
        purchase = self.purchase_main_tree.item(selected_item)['values']
        for i, key in enumerate(self.purchase_main_entries):
            self.purchase_main_entries[key].delete(0, tk.END)
            self.purchase_main_entries[key].insert(0, purchase[i])

    def export_purchase_main(self):
        import csv
        with open('purchase_main.csv', 'w', newline='') as file:
            writer = csv.writer(file)
            writer.writerow([col for col in self.purchase_main_entries])
            for purchase in self.db.fetch_all("SELECT * FROM tb_pay_main"):
                writer.writerow(purchase)
        messagebox.showinfo("Export", "Purchase main data exported to purchase_main.csv")




    ##########         采购明细表                ########################
    def create_purchase_detail_tab(self):
        self.purchase_detail_tab = ttk.Frame(self.notebook)
        self.notebook.add(self.purchase_detail_tab, text='   采购明细表管理   ')

        form_frame = ttk.Frame(self.purchase_detail_tab)
        form_frame.pack(fill="both", expand="yes", padx=20, pady=10)

        detail_form_frame = ttk.LabelFrame(form_frame, text="  采购明细表单  ")
        detail_form_frame.pack(side="right", fill="both", expand=True, padx=10, pady=10)
        self.purchase_detail_form(detail_form_frame)

        detail_list_frame = ttk.LabelFrame(self.purchase_detail_tab, text="  采购明细列表  ")
        detail_list_frame.pack(fill="both", expand="yes", padx=20, pady=10)
        self.purchase_detail_list(detail_list_frame)

        search_frame = ttk.Frame(self.purchase_detail_tab)
        search_frame.pack(fill="x", padx=20, pady=10)

        self.purchase_detail_search_label = ttk.Label(search_frame, text="搜索采购明细ID:")
        self.purchase_detail_search_label.pack(side="left", padx=(0, 10))

        self.purchase_detail_search_entry = ttk.Entry(search_frame)
        self.purchase_detail_search_entry.pack(side="left", padx=(0, 10))

        self.purchase_detail_search_button = ttk.Button(search_frame, text="搜索", command=self.search_purchase_detail)
        self.purchase_detail_search_button.pack(side="left")

    def purchase_detail_form(self, frame):
        labels = ["采购明细ID", "采购ID", "产品ID", "采购数量", "备注"]
        self.purchase_detail_entries = {}

        for i, label in enumerate(labels):
            ttk.Label(frame, text=label).grid(row=i, column=0, padx=10, pady=5, sticky="w")
            entry = ttk.Entry(frame, width=30)
            entry.grid(row=i, column=1, padx=10, pady=5, sticky="w")
            self.purchase_detail_entries[label] = entry

        ttk.Button(frame, text="添加", command=self.add_purchase_detail).grid(row=len(labels), column=0, padx=10,
                                                                              pady=10)
        ttk.Button(frame, text="更新", command=self.update_purchase_detail).grid(row=len(labels), column=1, padx=10,
                                                                                 pady=10)

    def purchase_detail_list(self, frame):
        columns = ["采购明细ID", "采购ID", "产品ID", "采购数量", "商品单价", "商品总价", "备注"]
        self.purchase_detail_tree = ttk.Treeview(frame, columns=columns, show='headings')
        self.purchase_detail_tree.pack(fill="both", expand=True)

        tree_frame = ttk.Frame(frame)
        tree_frame.pack(fill="both", expand=True)



        hsb = ttk.Scrollbar(tree_frame, orient="horizontal", command=self.purchase_detail_tree.xview)
        self.purchase_detail_tree.configure(xscrollcommand=hsb.set)
        hsb.pack(side='bottom', fill='x')

        self.purchase_detail_tree.pack(fill="both", expand=True)
        self.purchase_detail_tree.bind("<MouseWheel>", self._on_mousewheel)

        for col in columns:
            self.purchase_detail_tree.heading(col, text=col)
            self.purchase_detail_tree.column(col, width=100)

        self.load_purchase_detail()
        self.purchase_detail_tree.bind("<Double-1>", self.on_purchase_detail_tree_select)

        ttk.Button(frame, text="删除", command=self.delete_purchase_detail).pack(side=tk.RIGHT, padx=10, pady=10)
        ttk.Button(frame, text="导出为csv文件", command=self.export_purchase_detail).pack(side=tk.RIGHT, padx=10, pady=10)



    def search_purchase_detail(self):
        search_term = self.purchase_detail_search_entry.get().lower()

        for item in self.purchase_detail_tree.get_children():
            self.purchase_detail_tree.item(item, tags=())

        for item in self.purchase_detail_tree.get_children():
            purchase_detail = self.purchase_detail_tree.item(item, "values")
            if search_term in purchase_detail[0].lower():
                self.purchase_detail_tree.item(item, tags=('found',))
                self.purchase_detail_tree.selection_set(item)
                self.purchase_detail_tree.see(item)
                return

        messagebox.showinfo("搜索结果", "未找到匹配的采购明细ID")


    def load_purchase_detail(self):
        for i in self.purchase_detail_tree.get_children():
            self.purchase_detail_tree.delete(i)

        details = self.db.fetch_all("SELECT * FROM tb_pay_detail")
        for detail in details:
            self.purchase_detail_tree.insert('', tk.END, values=detail)

    def validate_purchase_detail_fields(self, data):
        required_fields = ["采购明细ID", "采购ID", "产品ID"]
        empty_fields = [key for i, key in enumerate(self.purchase_detail_entries) if
                        key in required_fields and not data[i]]

        if empty_fields:
            messagebox.showerror("错误", f"以下字段不能为空: {', '.join(empty_fields)}")
            return False
        return True

    def add_purchase_detail(self):
        data = [entry.get() for entry in self.purchase_detail_entries.values()]

        # 设置 Pcount 默认值为 1
        if not data[3]:  # 如果 data[3] 为空
            data[3] = 1
        else:
            try:
                data[3] = float(data[3])
            except ValueError:
                messagebox.showerror("错误", "数量必须是数字")
                return

        if not self.validate_purchase_detail_fields(data):
            return

        query_1 = "SELECT GPay, GName FROM tb_good WHERE Gid = %s"
        result = self.db.fetch_all(query_1, (data[2],))

        if result:
            gpay = result[0][0]  # 获取 GPay 的值
            gname = result[0][1]  # 获取 GName 的值
            try:
                total = data[3] * gpay  # 假设 data[3] 是一个数字
            except ValueError:
                messagebox.showerror("错误", "数量必须是数字")
                return

            data.extend([gpay, total])

            # Check if the record exists in tb_good_inventory
            query_2 = "SELECT COUNT(*) FROM tb_good_inventory WHERE Gid = %s"
            inventory_result = self.db.fetch_all(query_2, (data[2],))

            if inventory_result[0][0] == 0:
                # Insert the new record into tb_good_inventory
                query_insert_inventory = """
                INSERT INTO tb_good_inventory (Gid, GName, GPay, Ginventory, Gsell) 
                VALUES (%s, %s, %s, %s, %s)
                """
                self.db.execute_query(query_insert_inventory, (data[2], gname, gpay, data[3], 0))
            else:
                # Update the inventory if it exists
                query_inventory = "SELECT Ginventory FROM tb_good_inventory WHERE Gid = %s"
                result = self.db.fetch_all(query_inventory, (data[2],))
                new_inventory = float(result[0][0]) + data[3]
                query_update_inventory = "UPDATE tb_good_inventory SET Ginventory = %s WHERE Gid = %s"
                self.db.execute_query(query_update_inventory, (new_inventory, data[2]))

            query = """
            INSERT INTO tb_pay_detail (PDid, Pid, Gid, Pcount, other, GPay, total)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            """
            self.db.execute_query(query, data)
            self.load_inventory()
            self.load_purchase_detail()
            self.load_purchase_main()
        else:
            messagebox.showerror("错误", f"Gid {data[2]} 在 tb_good 中不存在")

    # def add_purchase_detail(self):
    #     data = [entry.get() for entry in self.purchase_detail_entries.values()]
    #
    #     if not self.validate_purchase_detail_fields(data):
    #         return
    #
    #     query_1 = "SELECT GPay, GName FROM tb_good WHERE Gid = %s"
    #     result = self.db.fetch_all(query_1, (data[2],))
    #
    #     if result:
    #         gpay = result[0][0]  # 获取 GPay 的值
    #         gname = result[0][1]  # 获取 GName 的值
    #         total = float(data[3]) * gpay
    #         # try:
    #         #     total = float(data[3]) * gpay  # 假设 data[3] 是一个数字
    #         # except ValueError:
    #         #     messagebox.showerror("错误", "数量必须是数字")
    #         #     return
    #
    #         data.extend([gpay, total])
    #
    #         # Check if the record exists in tb_good_inventory
    #         query_2 = "SELECT COUNT(*) FROM tb_good_inventory WHERE Gid = %s"
    #         inventory_result = self.db.fetch_all(query_2, (data[2],))
    #
    #         if inventory_result[0][0] == 0:
    #             # Insert the new record into tb_good_inventory
    #             query_insert_inventory = """
    #             INSERT INTO tb_good_inventory (Gid, GName, GPay, Ginventory, Gsell)
    #             VALUES (%s, %s, %s, %s, %s)
    #             """
    #             self.db.execute_query(query_insert_inventory, (data[2], gname, gpay, data[3], 0))
    #         else:
    #             # Update the inventory if it exists
    #             query_inventory = "SELECT Ginventory FROM tb_good_inventory WHERE Gid = %s"
    #             result = self.db.fetch_all(query_inventory, (data[2],))
    #             new_inventory = float(result[0][0]) + float(data[3])
    #             query_update_inventory = "UPDATE tb_good_inventory SET Ginventory = %s WHERE Gid = %s"
    #             self.db.execute_query(query_update_inventory, (new_inventory, data[2]))
    #
    #         query = """
    #         INSERT INTO tb_pay_detail (PDid, Pid, Gid, Pcount, other, GPay, total)
    #         VALUES (%s, %s, %s, %s, %s, %s, %s)
    #         """
    #         self.db.execute_query(query, data)
    #         self.load_inventory()
    #         self.load_purchase_detail()
    #         self.load_purchase_main()
    #     else:
    #         messagebox.showerror("错误", f"Gid {data[2]} 在 tb_good 中不存在")

    def update_purchase_detail(self):
        selected_item = self.purchase_detail_tree.selection()[0]
        data = [entry.get() for entry in self.purchase_detail_entries.values()]

        if not self.validate_purchase_detail_fields(data):
            return

        # 获取选中项的主键值
        data.append(self.purchase_detail_tree.item(selected_item)['values'][0])
        data.pop(0)

        # 从 tb_good 表中获取 GPay 值
        query_1 = "SELECT GPay FROM tb_good WHERE Gid = %s"
        result = self.db.fetch_all(query_1, (data[1],))

        if result:
            gpay = result[0][0]
            try:
                total = float(data[2]) * gpay
            except ValueError:
                messagebox.showerror("错误", "数量必须是数字")
                return

            data = data[:3] + [gpay, total] + data[3:]
            print(data)

            # 调用存储过程更新 tb_pay_detail 和 tb_good_inventory
            procedure_call = """
            CALL UpdatePurchaseDetail(%s, %s, %s, %s, %s, %s, %s)
            """
            self.db.execute_query(procedure_call, tuple(data))

            self.load_inventory()
            self.load_purchase_detail()
            self.load_purchase_main()
        else:
            messagebox.showerror("错误", f"Gid {data[1]} 在 tb_good 中不存在")

    def delete_purchase_detail(self):
        selected_item = self.purchase_detail_tree.selection()[0]
        PDid = self.purchase_detail_tree.item(selected_item)['values'][0]
        query = "DELETE FROM tb_pay_detail WHERE PDid=%s"
        self.db.execute_query(query, (PDid,))
        self.load_purchase_detail()
        self.load_purchase_main()

    def on_purchase_detail_tree_select(self, event):
        selected_item = self.purchase_detail_tree.selection()[0]
        detail = self.purchase_detail_tree.item(selected_item)['values']

        # 过滤掉“商品单价”和“商品总价”列数据
        form_keys = ["采购明细ID", "采购ID", "产品ID", "采购数量", "备注"]

        # 创建一个过滤后的表单值列表，只包含需要的列
        filtered_detail = [detail[self.purchase_detail_tree["columns"].index(col)] for col in form_keys]

        for i, key in enumerate(form_keys):
            self.purchase_detail_entries[key].delete(0, tk.END)
            self.purchase_detail_entries[key].insert(0, filtered_detail[i])

    def export_purchase_detail(self):
        import csv
        with open('purchase_detail.csv', 'w', newline='') as file:
            writer = csv.writer(file)
            writer.writerow([col for col in self.purchase_detail_entries])
            for detail in self.db.fetch_all("SELECT * FROM tb_pay_detail"):
                writer.writerow(detail)
        messagebox.showinfo("导出", "导出采购明细信息到 purchase_detail.csv")




####          商品价格区间查询    ##################
    def create_price_query_tab(self):
        self.price_query_tab = ttk.Frame(self.notebook)
        self.notebook.add(self.price_query_tab, text='   商品价格区间查询   ')

        query_form_frame = ttk.LabelFrame(self.price_query_tab, text="  查询条件  ")
        query_form_frame.pack(fill="both", expand="no", padx=20, pady=5)
        self.create_price_query_form(query_form_frame)

        query_result_frame = ttk.LabelFrame(self.price_query_tab, text="  查询结果  ")
        query_result_frame.pack(fill="both", expand="yes", padx=20, pady=10)
        self.create_price_query_result(query_result_frame)

    def create_price_query_form(self, frame):
        ttk.Label(frame, text="最低价格").grid(row=0, column=0, padx=10, pady=5, sticky="w")
        self.min_price_entry = ttk.Entry(frame, width=30)
        self.min_price_entry.grid(row=0, column=1, padx=10, pady=5, sticky="w")

        ttk.Label(frame, text="最高价格").grid(row=1, column=0, padx=10, pady=5, sticky="w")
        self.max_price_entry = ttk.Entry(frame, width=30)
        self.max_price_entry.grid(row=1, column=1, padx=10, pady=5, sticky="w")

        ttk.Button(frame, text="查询", command=self.query_price_range).grid(row=2, column=0, columnspan=2, padx=10,
                                                                            pady=10)

    def create_price_query_result(self, frame):
        columns = ["商品ID", "商品名", "价格", "供应商ID", "产品介绍", "备注"]
        self.price_query_tree = ttk.Treeview(frame, columns=columns, show='headings')
        self.price_query_tree.pack(fill="both", expand=True)

        for col in columns:
            self.price_query_tree.heading(col, text=col)
            self.price_query_tree.column(col, width=100)

    def query_price_range(self):
        try:
            min_price = float(self.min_price_entry.get())
            max_price = float(self.max_price_entry.get())

            for i in self.price_query_tree.get_children():
                self.price_query_tree.delete(i)

            # 调用存储过程并处理结果集
            results = self.db.fetch_all(
                '''SELECT * 
                         FROM tb_good 
                         WHERE GPay BETWEEN %s AND %s;''',
                (min_price, max_price))
            print(results)
            # 插入结果到 Treeview
            for row in results:
                self.price_query_tree.insert('', tk.END, values=row)



        except ValueError:
            messagebox.showerror("错误", "请输入有效的价格区间")
        except mysql.connector.Error as err:
            messagebox.showerror("错误", f"数据库错误: {err}")
        except Exception as e:
            messagebox.showerror("错误", f"发生了一个错误，错误信息: {e}")




####     库存管理    #######

    def create_good_inventory_tab(self):
        self.inventory_tab = ttk.Frame(self.notebook)
        self.notebook.add(self.inventory_tab, text='   库存管理   ')

        form_frame = ttk.LabelFrame(self.inventory_tab, text="  库存操作  ")
        form_frame.pack(fill="both", expand="yes", padx=20, pady=10)

        self.inventory_form(form_frame)

        # 添加搜索框和按钮
        search_frame = ttk.Frame(self.inventory_tab)
        search_frame.pack(fill="x", padx=10, pady=5)
        search_label = ttk.Label(search_frame, text="搜索商品名称:")
        search_label.pack(side="left")
        self.inventory_search_entry = ttk.Entry(search_frame)
        self.inventory_search_entry.pack(side="left", padx=5)
        search_button = ttk.Button(search_frame, text="搜索", command=self.search_inventory)
        search_button.pack(side="left")

        list_frame = ttk.LabelFrame(self.inventory_tab, text="  产品库存  ")
        list_frame.pack(fill="both", expand="yes", padx=20, pady=10)

        self.inventory_list(list_frame)

    def inventory_list(self, frame):
        columns = ["商品ID", "商品名", "价格", "库存量", "售出量"]
        self.inventory_tree = ttk.Treeview(frame, columns=columns, show='headings')
        self.inventory_tree.pack(fill="both", expand=True)

        for col in columns:
            self.inventory_tree.heading(col, text=col)
            self.inventory_tree.column(col, width=100)

        self.load_inventory()
        self.inventory_tree.bind("<Double-1>", self.on_inventory_tree_select)

        #貌似以下两句用不到
        # ttk.Button(frame, text="删除", command=self.delete_inventory).pack(side=tk.RIGHT, padx=10, pady=10)
        # ttk.Button(frame, text="导出为CSV文件", command=self.export_products).pack(side=tk.RIGHT, padx=10, pady=10)


    def search_inventory(self):
        search_term = self.inventory_search_entry.get().lower()

        for item in self.inventory_tree.get_children():
            self.inventory_tree.item(item, tags=())

        for item in self.inventory_tree.get_children():
            inventory = self.inventory_tree.item(item, "values")
            if search_term in inventory[1].lower():
                self.inventory_tree.item(item, tags=('found',))
                self.inventory_tree.selection_set(item)
                self.inventory_tree.see(item)
                return

        messagebox.showinfo("搜索结果", "未找到匹配的采购明细ID")

    def load_inventory(self):
        for i in self.inventory_tree.get_children():
            self.inventory_tree.delete(i)

        inventorys = self.db.fetch_all("SELECT * FROM tb_good_inventory")
        for inventory in inventorys:
            self.inventory_tree.insert('', tk.END, values=inventory)

    def on_inventory_tree_select(self, event):
        selected_item = self.inventory_tree.selection()[0]
        inventory = self.inventory_tree.item(selected_item)['values']
        for i, key in enumerate(self.inventory_entries):
            self.inventory_entries[key].delete(0, tk.END)
            self.inventory_entries[key].insert(0, inventory[i])

    def inventory_form(self, frame):
        labels = ["商品ID", "商品名", "价格", "库存量", "售出量"]
        self.inventory_entries = {}

        for i, label in enumerate(labels):
            ttk.Label(frame, text=label).grid(row=i, column=0, padx=10, pady=5, sticky="w")
            entry = ttk.Entry(frame, width=30)
            entry.grid(row=i, column=1, padx=10, pady=5, sticky="w")
            self.inventory_entries[label] = entry

        ##
        #ttk.Button(frame, text="添加", command=self.add_product).grid(row=len(labels), column=0, padx=10, pady=10)
        ttk.Button(frame, text="更新", command=self.update_inventory).grid(row=len(labels), column=1, padx=10, pady=10)

    def update_inventory(self):
        try:
            selected_item = self.inventory_tree.selection()[0]
            data = [entry.get() for entry in self.inventory_entries.values()]
            gid = self.inventory_tree.item(selected_item)['values'][0]
            print(gid)
            if not self.validate_inventory_fields_2(data):
                return
            print(data)
            # 获取当前库存和销量值
            query_select = "SELECT Ginventory, Gsell FROM tb_good_inventory WHERE Gid = %s"
            current_inventory_data = self.db.fetch_one(query_select, (gid,))
            print(current_inventory_data)
            if current_inventory_data:
                current_inventory, current_sell = current_inventory_data
                print(current_inventory)

                if current_sell is None:
                    current_sell=0
                print(current_sell)
                # 计算新的库存值
                new_inventory = float(data[3]) - (float(data[4]) - (current_sell))

                # 更新数据库
                query_update = """
                UPDATE tb_good_inventory SET GName=%s, GPay=%s, Ginventory=%s, Gsell=%s
                WHERE Gid=%s
                """
                update_data = (data[1], data[2], new_inventory, data[4], gid)
                self.db.execute_query(query_update, update_data)
                self.load_inventory()
                messagebox.showinfo("成功", "产品信息已更新")
            else:
                messagebox.showerror("错误", "无法找到选中的产品")

        except mysql.connector.Error as err:
            messagebox.showerror("错误", f"数据库错误: {err}")
        except Exception as e:
            messagebox.showerror("错误", f"发生了一个错误，错误信息: {e}")

    def validate_inventory_fields_2(self, data):
        try:
            float(data[2])  # Ginventory
            float(data[3])  # Gsell
            return True
        except ValueError:
            messagebox.showerror("错误", "库存量和销量必须是数字")
            return False

    def validate_inventory_fields(self, data):
        required_fields = ["商品id", "商品名", "单价", "库存量"]
        empty_fields = [key for i, key in enumerate(self.inventory_entries) if key in required_fields and not data[i]]

        if empty_fields:
            messagebox.showerror("错误", f"以下字段不能为空: {', '.join(empty_fields)}")
            return False
        return True

if __name__ == "__main__":
    login_window = LoginWindow()
    login_window.mainloop()
    # app = Application()
    # app.mainloop()
